﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.ComponentModel;
using System.Windows.Forms;
using System.Collections;
using CalemShop.Classes;

namespace CalemShop.Forms
{
    public class DataManager
    {
        public static DataManager Instance = new DataManager();
        private DataSet dataset = new DataSet("calemshop");
        private Hashtable autoKeyMaping = null;
        public AutoCompleteStringCollection ItemKeyCollection = new AutoCompleteStringCollection();
        private static Hashtable branchItemCached = new Hashtable();
        private static Hashtable serialStaffMap = null;
        private static Hashtable serialCustomerMap = new Hashtable();

        public DataTable LoadStaffs()
        {
            string tableName = "sys_user";
            DataTable tb = new DataTable(tableName);
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                MySqlDataAdapter adap = new MySqlDataAdapter("select * from sys_user", conn);
                adap.Fill(tb);
                return tb;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }


        public DataTable LoadItems()
        {
            string tableName = "shop_stock";
            DataTable tb = dataset.Tables[tableName];
            if (tb != null)
            {
                return tb;
            }

            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCmd = new MySqlCommand("select * from shop_stock left JOIN item_type on (item_type.typeId = shop_stock.type)");
                selectCmd.Connection = conn;
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCmd);
                adap.Fill(dataset, tableName);
                MySqlDataReader rs = selectCmd.ExecuteReader();
                autoKeyMaping = new Hashtable();
                while (rs.Read())
                {
                    ItemKeyCollection.Add(rs.GetString("itemId"));
                    ItemKeyCollection.Add(rs.GetString("autoKey"));
                    ItemKeyCollection.Add(rs.GetString("supplier"));
                    ItemKeyCollection.Add(rs.GetString("color"));
                    autoKeyMaping[rs.GetString("itemId").ToUpper()] = rs.GetInt32("autoKey");
                }

                tb = dataset.Tables[tableName];
                tb.PrimaryKey = new DataColumn[] { tb.Columns["autoKey"] };
                return tb;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }

        public int GetItemAutokeyByItemId(string itemId)
        {
            if (autoKeyMaping == null)
            {
                LoadItems();
            }

            object value = autoKeyMaping[itemId];
            if (value == null)
            {
                return 0;
            }
            return (int)value;
        }

        public ItemEntity BranchGetItemFromStock(int itemAutoKey)
        {
            object co = branchItemCached[itemAutoKey];
            if (co != null)
            {
                return (ItemEntity)co;
            }

            MySqlConnection conn = DBBranchConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `shop_stock`.`autoKey`=?", conn);
                selectCommand.Parameters.AddWithValue("autoKey", itemAutoKey);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                ItemEntity item = null;
                if (rs.Read())
                {
                    item = new ItemEntity();
                    item.id = rs.GetString("itemId");
                    item.name = rs.GetString("itemName");
                    item.desc = rs.GetString("desc");
                    item.color = rs.GetString("color");
                    item.size = rs.GetString("size");
                    item.supplier = rs.GetString("supplier");
                    item.gender = rs.GetString("gender");
                    item.typeName = rs.GetString("typeName");
                    item.cost = rs.GetInt32("cost");
                    item.price = rs.GetInt32("price");
                    item.giasi = rs.GetInt32("giasi");
                    item.amount = rs.GetInt32("amount");
                    item.autoKey = rs.GetInt32("autoKey");
                    item.type = rs.GetInt32("typeId");
                }

                rs.Close();
                selectCommand.Dispose();
                branchItemCached[itemAutoKey] = item;
                return item;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }
        }

        public bool BranchAddItemToStock(ItemEntity item, int amount)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand UpdateCommand = new MySqlCommand("insert into shop_stock(`autoKey`, `itemId`, `itemName`, `desc`, `color`, `gender`, `size`, `amount`, `price`, `cost`, `giasi`, `type`, `branch`,`createTime`) values(?,?,?,?,?,?,?,?,?,?,?,?,0,?) ON DUPLICATE KEY UPDATE `amount` = `amount` + ?, `price` = ?, `cost` = ?;", conn);
                UpdateCommand.Parameters.AddWithValue("autoKey", item.autoKey);
                UpdateCommand.Parameters.AddWithValue("itemId", item.id);
                UpdateCommand.Parameters.AddWithValue("itemName", item.name);
                UpdateCommand.Parameters.AddWithValue("desc", item.desc);
                UpdateCommand.Parameters.AddWithValue("color", item.color);
                UpdateCommand.Parameters.AddWithValue("gender", item.gender.ToUpper());
                UpdateCommand.Parameters.AddWithValue("size", item.size);

                UpdateCommand.Parameters.AddWithValue("amount", amount);
                UpdateCommand.Parameters.AddWithValue("price", item.price);
                UpdateCommand.Parameters.AddWithValue("cost", item.cost);
                UpdateCommand.Parameters.AddWithValue("giasi", item.giasi);
                UpdateCommand.Parameters.AddWithValue("type", item.type);
                UpdateCommand.Parameters.AddWithValue("createTime", DateTime.Now.ToString("yyyy-MM-dd"));
                // Update if duplicate
                UpdateCommand.Parameters.AddWithValue("amount2", amount);
                UpdateCommand.Parameters.AddWithValue("price2", item.price);
                UpdateCommand.Parameters.AddWithValue("cost2", item.cost);
                return UpdateCommand.ExecuteNonQuery() > 0;
            }
            catch (Exception e)
            {
                Reporter.WriteErrorEx(e);
                return false;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public bool BranchUpdateItemAfterTransfer(int itemAutoKey, int amount)
        {
            MySqlConnection conn = DBBranchConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand UpdateCommand = new MySqlCommand("update shop_stock set `amount` = `amount` - ? where `autoKey` = ?", conn);
                UpdateCommand.Parameters.AddWithValue("amount", amount);
                UpdateCommand.Parameters.AddWithValue("autoKey", itemAutoKey);
                return UpdateCommand.ExecuteNonQuery() > 0;
            }
            catch (Exception e)
            {
                Reporter.WriteErrorEx(e);
                return false;
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }

        }

        public static int BranchExecuteQuerys(ArrayList sqlList, BackgroundWorker bw)
        {
            if (sqlList.Count == 0)
            {
                return 0;
            }

            MySqlConnection conn = DBBranchConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                int totalCount = 0;
                int count = 0;
                bool needCommit = false;
                foreach (string sql in sqlList)
                {
                    if (string.IsNullOrEmpty(sql))
                    {
                        continue;
                    }

                    MySqlCommand inserCmd = new MySqlCommand(sql, conn, trans);
                    needCommit = true;
                    if (inserCmd.ExecuteNonQuery() > 0)
                    {
                        totalCount++;
                    }
                    count++;
                    inserCmd.Dispose();
                    if (count > 0 && count == 3)
                    {
                        count = 0;
                        trans.Commit();
                        trans = conn.BeginTransaction();
                    }

                    if (bw != null)
                    {
                        bw.ReportProgress(totalCount);
                    }
                }

                if (needCommit)
                {
                    trans.Commit();
                }
                return totalCount;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return -1;
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }

        }

        public static long BranchGetLastItemIdInStock()
        {
            MySqlConnection conn = DBBranchConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select max(autoKey) as lastId from shop_stock;", conn);
                string snum = selectCommand.ExecuteScalar().ToString();
                if (string.IsNullOrEmpty(snum))
                {
                    selectCommand.Dispose();
                    return 0;
                }
                long id = long.Parse(snum);
                selectCommand.Dispose();
                return id;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }
        }

        public static DataTable BranchGetStockBy(string typeFilter)
        {
            MySqlConnection conn = DBBranchConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select * from shop_stock " + typeFilter + " order by autoKey asc";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }

            return null;

        }

        public static int BranchExecuteQuerys(params string[] sqlList)
        {
            if (sqlList.Length == 0)
            {
                return 0;
            }

            MySqlConnection conn = DBBranchConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                int totalCount = 0;
                int count = 0;
                bool needCommit = false;
                foreach (string sql in sqlList)
                {
                    if (string.IsNullOrEmpty(sql))
                    {
                        continue;
                    }

                    MySqlCommand inserCmd = new MySqlCommand(sql, conn, trans);
                    needCommit = true;
                    if (inserCmd.ExecuteNonQuery() > 0)
                    {
                        totalCount++;
                    }
                    count++;
                    inserCmd.Dispose();
                    if (count > 0 && count == 3)
                    {
                        count = 0;
                        trans.Commit();
                        trans = conn.BeginTransaction();
                    }
                }

                if (needCommit)
                {
                    trans.Commit();
                }
                return totalCount;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return 0;
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }

        }

        public static ItemEntity BranchGetStockItem(int autoKey)
        {
            MySqlConnection conn = DBBranchConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `shop_stock`.`autoKey`=?", conn);
                selectCommand.Parameters.AddWithValue("autoKey", autoKey);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                ItemEntity item = null;
                if (rs.Read())
                {
                    item = new ItemEntity();
                    item.id = rs.GetString("itemId");
                    item.name = rs.GetString("itemName");
                    item.desc = rs.GetString("desc");
                    item.color = rs.GetString("color");
                    item.size = rs.GetString("size");
                    item.supplier = rs.GetString("supplier");
                    item.gender = rs.GetString("gender");
                    item.typeName = rs.GetString("typeName");
                    item.cost = rs.GetInt32("cost");
                    item.price = rs.GetInt32("price");
                    item.giasi = rs.GetInt32("giasi");
                    item.amount = rs.GetInt32("amount");
                    item.autoKey = rs.GetInt32("autoKey");
                    item.type = rs.GetInt32("typeId");
                }

                rs.Close();
                selectCommand.Dispose();
                return item;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBBranchConnection.releaseConnection(conn);
            }
        }

        public static DataTable GetAllCustomer()
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select customerId, customerName, serial, phone, createTime, point from customer_new";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static long AddNewCustomer(string name, string phone, int point, string serial)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                string sql = "insert ignore into customer_new(customerName,phone,point,serial,createTime) values(?,?,?,?, NOW());";
                conn.Open();
                MySqlCommand insertCmd = new MySqlCommand(sql, conn);
                insertCmd.Parameters.AddWithValue("customerName", name);
                insertCmd.Parameters.AddWithValue("phone", phone);
                insertCmd.Parameters.AddWithValue("point", point);
                insertCmd.Parameters.AddWithValue("serial", serial);
                insertCmd.ExecuteNonQuery();
                long id = insertCmd.LastInsertedId;
                insertCmd.Dispose();
                return id;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static bool UpdateCustomer(int customerId, string name, int point, string serial)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                string sql = "";
                conn.Open();
                MySqlCommand updateCmd = null;
                if (string.IsNullOrEmpty(serial))
                {
                    sql = "update customer_new set customerName = ? ,point = point + ? where customerId = ?";
                    updateCmd = new MySqlCommand(sql, conn);
                    updateCmd.Parameters.AddWithValue("customerName", name);
                    updateCmd.Parameters.AddWithValue("point", point);
                    updateCmd.Parameters.AddWithValue("customerId", customerId);
                }
                else
                {
                    sql = "update customer_new set customerName = ? ,point = point + ?, serial = ? where customerId = ?";
                    updateCmd = new MySqlCommand(sql, conn);
                    updateCmd.Parameters.AddWithValue("customerName", name);
                    updateCmd.Parameters.AddWithValue("point", point);
                    updateCmd.Parameters.AddWithValue("serial", serial);
                    updateCmd.Parameters.AddWithValue("customerId", customerId);
                }

                updateCmd.ExecuteNonQuery();
                updateCmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }

        public static bool UpdateCustomerPoint(int customerId, int point)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                string sql = "";
                conn.Open();
                MySqlCommand updateCmd = null;
                sql = "update customer_new set point = point + ? where customerId = ?";
                updateCmd = new MySqlCommand(sql, conn);
                updateCmd.Parameters.AddWithValue("point", point);
                updateCmd.Parameters.AddWithValue("customerId", customerId);
                updateCmd.ExecuteNonQuery();
                updateCmd.Dispose();
                return true;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }

        public static Customer GetCustomerBySerial(string serial)
        {
            if (string.IsNullOrEmpty(serial))
            {
                return null;
            }

            if (serialCustomerMap.ContainsKey(serial))
            {
                return (Customer)serialCustomerMap[serial];
            }

            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                Customer cs = null;
                string sql = "select * from customer_new where serial = ?";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("serial", serial);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                if (rs.Read())
                {
                    cs = new Customer();
                    cs.customerId = rs.GetInt32("customerId");
                    cs.customerName = rs.GetString("customerName");
                    cs.serial = rs.GetString("serial");
                    cs.phone = rs.GetString("phone");
                    cs.point = rs.GetInt32("point");
                    serialCustomerMap[serial] = cs;
                }
                rs.Close();
                selectCommand.Dispose();
                return cs;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static bool DeleteCustomer(long customerId)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                string sql = "delete from customer_new where customerId = ?";
                conn.Open();
                MySqlCommand deleteCmd = new MySqlCommand(sql, conn);
                deleteCmd.Parameters.AddWithValue("customerId", customerId);
                bool result = deleteCmd.ExecuteNonQuery() > 0;
                deleteCmd.Dispose();
                return result;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }

        public static DataTable GetStaffWorkingTimeToday(string toDate)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select staffName, workDate, FROM_UNIXTIME(startTime) as startWorkAt, FROM_UNIXTIME(endTime) as stopWorkAt,(endTime - startTime) as workingTime from work_time where DATE(workDate) = DATE(?)";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("toDate", toDate);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static ListViewItem[] GetStaffWorkingTime(string fromDate, string toDate)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select staffName, (endTime - startTime) as workingTime from work_time where DATE(workDate) >= DATE(?) and DATE(workDate) <= DATE(?)";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("fromDate", fromDate);
                selectCommand.Parameters.AddWithValue("toDate", toDate);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                Hashtable hash = new Hashtable();
                while (rs.Read())
                {
                    string key = rs.GetString("staffName");
                    object co = hash[key];
                    if (co != null)
                    {
                        ListViewItem item = (ListViewItem)co;
                        long time = 0;
                        long.TryParse(item.SubItems[1].Tag.ToString(), out time);
                        time += rs.GetInt64("workingTime");
                        double d = Math.Round(time / 3600f, 1);
                        item.SubItems[1].Text = d.ToString();
                        item.SubItems[1].Tag = time;
                    }
                    else
                    {
                        long time = rs.GetInt64("workingTime");
                        double d = Math.Round(time / 3600f, 1);
                        ListViewItem item = new ListViewItem(new string[] {
                            key, d.ToString()
                        });


                        item.SubItems[1].Tag = time;
                        hash.Add(key, item);
                        item.ImageIndex = 0;
                    }
                }

                rs.Close();
                selectCommand.Dispose();
                ListViewItem[] list = new ListViewItem[hash.Count];
                hash.Values.CopyTo(list, 0);
                return list;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static bool UpdateStaffWorkingTime(string staffName)
        {
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("insert into work_time(staffName,workDate,startTime,endTime) values (?,DATE(NOW()),UNIX_TIMESTAMP(),UNIX_TIMESTAMP()) ON DUPLICATE KEY UPDATE endTime=UNIX_TIMESTAMP()", conn);
                selectCommand.Parameters.AddWithValue("staffName", staffName);
                selectCommand.ExecuteNonQuery();
                selectCommand.Dispose();
                return true;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }


        public static Hashtable GetStaffSerialMap()
        {
            if (serialStaffMap != null)
            {
                return serialStaffMap;
            }


            serialStaffMap = new Hashtable();
            MySqlConnection conn = DBConnection.getGlobalConnection();
            try
            {
                conn.Open();
                MySqlCommand select = new MySqlCommand("select * from sys_user", conn);
                MySqlDataReader rs = select.ExecuteReader();
                while (rs.Read())
                {

                    string serial = "";
                    if (rs["serial"] != null)
                    {
                        serial = rs["serial"].ToString();
                    }

                    serialStaffMap.Add(serial, rs["account"]);
                }

                rs.Close();
                return serialStaffMap;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }
    }
}
